﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PACKETPARSERMAX20.CORE;

namespace PACKETPARSERMAX20.CONSOLE
{
    public static class ConvertBinToSQLite
    {
        public static void ConvertFiles(string pSourceDir, string pDestDir)
        {
            string[] files = System.IO.Directory.GetFiles(pSourceDir, "*.bin", System.IO.SearchOption.AllDirectories);

            foreach (string file in files)
            {
                Convert(pDestDir, file);
            }
        }

        public static void Convert(string pDestDir, string pFileName)
        {
            BinaryFileReader bfr = new BinaryFileReader(pFileName);

            var _SMSG_AUTH_CHALLENGE = bfr.GetNextPacket();
            var _CMSG_AUTH_SESSION = bfr.GetNextPacket();
            var _SMSG_AUTH_RESPONSE = bfr.GetNextPacket();

            PACKETPARSERMAX20.CORE.QuickStream qs = new PACKETPARSERMAX20.CORE.QuickStream();

            qs.Load(_CMSG_AUTH_SESSION.Data);

            var _ClientBuild = qs.ReadUInt16();
            var _unknown1 = qs.ReadUInt16();
            var _unknown2 = qs.ReadUInt32();
            var _accountName = qs.ReadString();
            var _clientSeed = qs.ReadUInt32();


            string newfile = pDestDir + string.Format("{1}_{2}_{3}.sqlite", System.IO.Path.GetDirectoryName(pFileName), System.IO.Path.GetFileNameWithoutExtension(pFileName), _ClientBuild, _accountName);

            if (System.IO.File.Exists(newfile)) System.IO.File.Delete(newfile);
            Console.WriteLine(newfile);

            System.Data.SQLite.SQLiteConnection.CreateFile(newfile);

            System.Data.SQLite.SQLiteConnectionStringBuilder builder = new System.Data.SQLite.SQLiteConnectionStringBuilder();
            builder.DataSource = newfile;
            builder.CacheSize = builder.CacheSize * 100;
            builder.PageSize = builder.PageSize * 100;
            builder.JournalMode = System.Data.SQLite.SQLiteJournalModeEnum.Off;
            builder.Pooling = false;

            DateTime tstart = DateTime.Now;
            using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(builder.ConnectionString))
            {
                con.Open();

                //create tables
                StringBuilder sb = new StringBuilder();

                sb.AppendLine("create table packets (id integer primary key autoincrement, timestamp datetime, direction integer, opcode integer, data blob);");
                sb.AppendLine("create table header (key string primary key, value string);");
                sb.AppendLine(string.Format("insert into header(key, value) values ('clientBuild', '{0}');", bfr.header.ClientBuild));
                sb.AppendLine("insert into header(key, value) values ('clientLang', 'enUS');");
                sb.AppendLine(string.Format("insert into header(key, value) values ('accountName', '{0}');", _accountName));
                sb.AppendLine("insert into header(key, value) values ('realmName', '');");
                sb.AppendLine("insert into header(key, value) values ('realmServer', '');");
                sb.AppendLine("insert into header(key, value) values ('snifferVersion', '');");

                using (System.Data.SQLite.SQLiteCommand command = con.CreateCommand())
                {
                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();
                }


                bfr.Reopen();

                Console.WriteLine("start processing newfile: {0} filename: {1}", tstart, newfile);

                try
                {

                    using (System.Data.SQLite.SQLiteTransaction dbTrans = con.BeginTransaction())
                    {
                        using (System.Data.SQLite.SQLiteCommand command = con.CreateCommand())
                        {
                            command.CommandText = "insert into packets (timestamp, direction, opcode, data) VALUES (?,?,?,?)";

                            System.Data.SQLite.SQLiteParameter timestamp = command.CreateParameter();
                            timestamp.DbType = System.Data.DbType.DateTime;
                            command.Parameters.Add(timestamp);

                            System.Data.SQLite.SQLiteParameter direction = command.CreateParameter();
                            direction.DbType = System.Data.DbType.Int32;
                            command.Parameters.Add(direction);

                            System.Data.SQLite.SQLiteParameter opcode = command.CreateParameter();
                            opcode.DbType = System.Data.DbType.Int32;
                            command.Parameters.Add(opcode);

                            System.Data.SQLite.SQLiteParameter data = command.CreateParameter();
                            data.DbType = System.Data.DbType.Binary;
                            command.Parameters.Add(data);


                            while (true)
                            {
                                Packet packet = bfr.GetNextPacket();
                                if (packet == null) break;
                                try
                                {
                                    timestamp.Value = packet.CreatedDate;
                                    direction.Value = packet.Direction;
                                    opcode.Value = packet.Opcode;
                                    data.Value = packet.Data;

                                    if (command.ExecuteNonQuery() <= 0)
                                    {
                                        throw new Exception("record not inserted?");
                                    }
                                }
                                catch (Exception exc)
                                {
                                    Console.WriteLine("Error: {0}", exc.Message);
                                }
                            }

                        }

                        dbTrans.Commit();

                    }
                }
                catch (Exception exc)
                {
                    Console.WriteLine("Error: {0}", exc.Message);

                }


                con.Close();



            }

            //if (failed)
            //{
            //    System.IO.File.Delete(newfile);
            //}
            //else
            //{
            //System.IO.File.Move(pFileName, @"H:\DATA\SQLITE\convertedbinary\" + System.IO.Path.GetFileName(pFileName));
            //System.IO.File.Move(tempfile, @"H:\DATA\SQLITE\convertedtemps\" + System.IO.Path.GetFileName(tempfile));

            //}

        }

        public static bool WritePacket(PACKETPARSERMAX20.CORE.Packet packet, System.Data.SQLite.SQLiteConnection con)
        {
            bool failed = false;
            try
            {



                using (System.Data.SQLite.SQLiteCommand command = con.CreateCommand())
                {
                    try
                    {
                        //"insert into packets (timestamp, direction, opcode, data) 
                        //" values 
                        //(datetime("+String.valueOf(packet.date.getTime()/1000)+",'unixepoch'),
                        //"+packet.direction+",
                        //"+packet.opcode+",
                        //X'"+ByteBufferConverter.getHexRepresentation(packet.payload, false)+"');";



                        command.Parameters.AddWithValue("@timestamp", packet.CreatedDate);
                        command.Parameters.AddWithValue("@direction", packet.Direction);
                        command.Parameters.AddWithValue("@opcode", packet.Opcode);
                        command.Parameters.AddWithValue("@data", packet.Data);

                        command.ExecuteNonQuery();
                    }
                    catch (Exception exc)
                    {
                        Console.WriteLine("Error: {0}", exc.Message);
                    }
                }


            }
            catch (Exception exc)
            {
                Console.WriteLine("Error: {0}", exc.Message);
                failed = true;
            }

            return failed;
        }


    }
}
